package com.topway.dsmflex.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.InvalidPropertiesFormatException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import java.util.Vector;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;

import org.apache.log4j.Logger;
import org.logicalcobwebs.proxool.ProxoolDataSource;
import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;
import org.logicalcobwebs.proxool.configuration.JAXPConfigurator;
import org.logicalcobwebs.proxool.configuration.PropertyConfigurator;

import com.topway.dsmflex.Log;
import com.topway.dsmflex.model.synchro.DataSourceBean;



/**
 * 数据连接池帮助类
 * 
 * @author liusy 2012-12-11
 */
public class JdbcHelp {
	
	   public static String DATE_FORMAT      = "yyyy-MM-dd";
	   public static String TIME_FORMAT      = "HH:mm:ss";
	   public static String TIMESTAMP_FORMAT = "yyyy-MM-dd HH:mm:ss";
	   public static String TIMESTAMP = "yyyy-MM-dd HH:mm:ss.S";
	   public static String ID_COLUMN        = "id";
	   public static String COUNT_COLUMN     = "cnt";
	   
	private static Logger log = Logger.getLogger(JdbcHelp.class);
	
	private static ThreadLocal<JdbcHelp> jdbcHelp= new ThreadLocal<JdbcHelp>();
	private static Properties _properties=  new Properties();
	

	private ConcurrentMap<String,Vector<Connection>> mapCon= new ConcurrentHashMap<String, Vector<Connection>>();
	
	public  static synchronized JdbcHelp getJdbcHelp()
	{
		if(null==jdbcHelp.get())
		{
			jdbcHelp.set(new JdbcHelp());
		}
		return jdbcHelp.get();
	}
	
	/**
	 * 加载proxool驱动，并注册连接
	 * 
	 */
	static {
//		try {
//			if(ProxoolFacade.getAliases().length==0)
//			{
//				Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
//				JAXPConfigurator.configure(System.getProperty("user.dir")+"/src/proxool.xml", false);
//			}
//		} catch (ProxoolException e) {
//			e.printStackTrace();
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		}
		try {
			_properties.load(new FileInputStream(System.getProperty("user.dir")+"/proxool.properties"));
		} catch (InvalidPropertiesFormatException e) {
			log.info(System.getProperty("user.dir")+"/proxool.properties"+"。。。模板。。。。。。格式 不正确！");
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			log.info(System.getProperty("user.dir")+"/proxool.properties"+"。。。模板。。。。。。不存在！");
			e.printStackTrace();
		} catch (IOException e) {
			log.info(System.getProperty("user.dir")+"/proxool.properties"+"。。。模板。。。。。。不能被加载！");
			e.printStackTrace();
		}
		for(Entry e:_properties.entrySet())
		{
			System.out.println(e.getKey()+":"+e.getValue());
		}
	}
	
	/**
	 * 从proxool中的数据裤连接池中得到联接
	 * @return
	 * @throws ProxoolException
	 */
	public synchronized Connection  getConnection(String alias) 
	{
		
		Connection cn=null;
		try {
			
			ProxoolDataSource p = new ProxoolDataSource(alias);
			cn =p.getConnection();
		} catch (Exception e) {
			log.error(e.getMessage());
		}
		return cn;
	}
	/**
	 * 从proxool中的数据裤连接池中得到联接
	 * @return
	 * @throws ProxoolException
	 */
	public Connection addConnection(String alias,Connection cn) 
	{
		try {
			mapCon.get(alias).add(cn);
		} catch (Exception e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return cn;
	}
	
	/**
	 * 查询方法
	 * 
	 * 会自己关闭 conn,rs,stmt
	 * 
	 * @param sql
	 * @param conn
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> getDataObject(String sql,Connection conn) {
	    Statement stmt = null;
	    ResultSet rs = null;
	    List<Map<String,Object>> list= new ArrayList<Map<String,Object>>();
	    Map<String, Object> map=null;
	    try {
	        stmt =   conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
	        rs = stmt.executeQuery(sql);
	        ResultSetMetaData rsmd = rs.getMetaData();
	        int numberOfColumns = rsmd.getColumnCount();
	        while(rs.next()) {
	            map = new HashMap<String, Object>(numberOfColumns);
	            for(int r=1;r<numberOfColumns+1;r++){
	                map.put(MapKeyConvert(rsmd.getColumnName(r)),rs.getObject(r));
	            }
	            list.add(map);
	        }
	     } catch (Exception ex) {
	            ex.printStackTrace();
	     }finally{
	     try{
	         if(rs!=null)rs.close();
	         if(stmt!=null)stmt.close();
	         if(conn!=null){
	             conn.close();
	         }
	     }catch(Exception e){
	         e.printStackTrace();
	     }
	     }
	     return list;
	}
	
	
	
	/**
	 * 查询方法
	 * 
	 * 不会关闭 rs=null,stmt=null
	 * 
	 * @param sql
	 * @param conn
	 * @return List<Map<String, Object>>
	 */
	public static List<Map<String, Object>> getDataObject(String sql,Statement stmt) {
	    ResultSet rs = null;
	    List<Map<String,Object>> list= new ArrayList<Map<String,Object>>();
	    Map<String, Object> map=null;
	    try {
	        rs = stmt.executeQuery(sql);
	        ResultSetMetaData rsmd = rs.getMetaData();
	        int numberOfColumns = rsmd.getColumnCount();
	        while(rs.next()) {
	            map = new HashMap<String, Object>(numberOfColumns);
	            for(int r=1;r<numberOfColumns+1;r++){
	                map.put(MapKeyConvert(rsmd.getColumnName(r)),rs.getObject(r));
	            }
	            list.add(map);
	        }
	     } catch (Exception ex) {
	            ex.printStackTrace();
	     }finally{
	     try{
//	         if(rs!=null)rs=null;
//	         if(stmt!=null)stmt=null;
	     }catch(Exception e){
	         e.printStackTrace();
	     }
	     }
	     return list;
	}
	
	private static String MapKeyConvert(String key_){
    	String keyName = key_.toLowerCase();
    	if(keyName.indexOf("_")>0)
    	{
        	String [] key = keyName.split("_");
        	for (int ii=0;ii<key.length;ii++) {
        		String  keyStr = key[ii];
        		if(key.length>0)
        		{
        			if(ii!=0)
        			keyName +=keyStr.substring(0,1).toUpperCase()+keyStr.substring(1,keyStr.length());
        			else
        			keyName = keyStr;
        		}
        	}
    	}
    	return keyName;
	}
	/**
	 * 执行存储过程
	 * 关闭 rs=null,stmt=null
	 * @param ct
	 * @param procName
	 * @param params
	 */
	public void callProc(Connection ct,String procName,Object [] params){
		 CallableStatement cs = null;
		 try{
			 String sql ="{call "+procName+"(";
			 if(params!=null){
			  for(int i=0;i<params.length;i++){
				  sql = sql+"?,";
			  }
			 }
			 sql = sql.substring(0, sql.length()-1)+")}";
			 cs = ct.prepareCall(sql);
			  if(params!=null){
				  for(int j=0;j<params.length;j++){
					  Object obj = params[j];
					  if(obj instanceof java.util.Date || obj instanceof java.sql.Date){
						  cs.setTimestamp(j+1,new java.sql.Timestamp(((Date)obj).getTime()));
					  }else
						  cs.setString(j+1,obj.toString());
				  }
			   }
			 cs.executeUpdate();
		 }
		 catch(Exception e){
			 e.printStackTrace();
			 try{
			   ct.rollback();
			 }catch(Exception ee){
				 ee.printStackTrace();
			 }
		 }
		 finally{
			 try{
				 cs.close();
				 ct.close();
			 }
			 catch(Exception eee){
				 eee.printStackTrace();
			 }
		 }
		  
	}
	
	public static Connection getDataSourceBean(DataSourceBean dataSourceBean) {
		 Connection  conn = null;
		try {
            //Class.forName(dataSourceBean.getDriver());
            //conn = DriverManager.getConnection(dataSourceBean.getUrl(),dataSourceBean.getUserName(),dataSourceBean.getPassword());
			conn = getProxoolConnection(dataSourceBean);
		} catch (Exception e) {
			log.error(dataSourceBean.getUrl()+"  失败");
			return conn; 
		}
		return conn;
		
		

	}
	
	public static Connection getConnection(DataSourceBean dataSourceBean)
	{
		Connection  conn = null;
        try {
//			Class.forName(dataSourceBean.getDriver());
//	        conn=DriverManager.getConnection(dataSourceBean.getUrl(),dataSourceBean.getUserName(),dataSourceBean.getPassword());
        	conn = getProxoolConnection(dataSourceBean);
		}  catch (SQLException e) {
			log.error(dataSourceBean.getUrl()+ "  "+e.getMessage());
			//Log.error("数据源名["+dataSourceBean.getDataSourceName()+"] 连接失败，导致系统无法使用，请检查 [ "+dataSourceBean.getUrl()+ "]!  ");
		}
		return conn;	
	}

	public static synchronized Connection getProxoolConnection(DataSourceBean dataSourceBean) throws SQLException {
		String [] aliases = ProxoolFacade.getAliases();
		Arrays.sort(aliases);
		if(Arrays.binarySearch(aliases, dataSourceBean.getDataSourceName())<0)
		{
			try {
				Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
				_properties.put("jdbc-0.proxool.alias", dataSourceBean.getDataSourceName());
				_properties.put("jdbc-0.proxool.driver-class", dataSourceBean.getDriver() );
				_properties.put("jdbc-0.proxool.driver-url", dataSourceBean.getUrl());
				_properties.put("jdbc-0.user", dataSourceBean.getUserName());
				_properties.put("jdbc-0.password", dataSourceBean.getPassword());
				PropertyConfigurator.configure(_properties);
			} catch (ProxoolException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
		}
		return DriverManager.getConnection("proxool."+dataSourceBean.getDataSourceName());
		//ProxoolDataSource proxoolDataSource= new ProxoolDataSource(dataSourceBean.getDataSourceName());
		//return proxoolDataSource.getConnection();
	}
	  
	
	
	
	public static void main(String[] args) {
		
		java.text.SimpleDateFormat df  = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
		
		System.out.println(df.format(new Date()));
		//System.out.println(ProxoolFacade.getAliases().length);
//    	String keyName = "DDD_ERC".toLowerCase();
//    	
//    	if(keyName.indexOf("_")>0)
//    	{
//        	String [] key = keyName.split("_");
//        	keyName = "";
//        	for (int ii=0;ii<key.length;ii++) {
//        		String  keyStr = key[ii];
//        		if(key.length>0)
//        		{
//        			if(ii!=0)
//        			keyName +=keyStr.substring(0,1).toUpperCase()+keyStr.substring(1,keyStr.length());
//        			else
//        			keyName = keyStr;
//        		}
//        	}
//    	}
//		System.out.println(keyName);
		
		
		
		
//			for(Entry e:System.getProperties().entrySet())
//		{
//			System.out.println(e.getKey()+":"+e.getValue());
//		}
	}
	
}
